<?php
namespace app\service;
use think\facade\Db;
use app\model\BuyModel;
use Exception;

class BuyService{  
    public function list($params){
        $where=[];
        $page = $params['page'];
        $pageCount = $params['page_count'];

        //按出发日期查询
        if(!empty($params['start_date']) && !empty($params['end_date'])){
            if(!empty($params['start_date'])){
                $where[] = ['a.start_date','>= time',$params['start_date']];  
            }
            if(!empty($params['end_date'])){
                $where[] = ['a.start_date','<= time',$params['end_date']];  
            }
        }  

        if(!empty($params['typeid'])){
            $where[] = ['a.type','=',$params['typeid']];   
        }
        if(!empty($params['keyword'])){
            $where[] = ['a.content|a.payment_no|a.sn|a.model','like',"%{$params['keyword']}%"];   
        }
        if(!empty($params['payment_no'])){
            $where[] = ['a.payment_no','=',$params['payment_no']];  
        }
        if(!empty($params['sn'])){
            $where[] = ['a.sn','=',$params['sn']];  
        }
        if(!empty($params['client_id'])){
            $where[] = ['a.client_id','=',$params['client_id']];  
        }
        if(!empty($params['supplier_id'])){
            $where[] = ['a.supplier_id','=',$params['supplier_id']];  
        }
        if(!empty($params['business_type_id'])){
            $where[] = ['a.business_type_id','=',$params['business_type_id']];  
        }
        if(!empty($params['branch_id'])){
            $where[] = ['a.branch_id','=',$params['branch_id']];  
        }
        if(!empty($params['category_id'])){
            $where[] = ['a.category_id','=',$params['category_id']];  
        }
        if(!empty($params['operator_id'])){
            $where[] = ['a.operator_id','=',$params['operator_id']];  
        }
        
        
        $data = BuyModel::alias('a')
            ->field('a.*,b.title as branch,c.title as business_type,d.company_name as client,e.supplier_name as supplier,f.supplier_name as supplier2,g.cn_name as operator,h.name as category,i.cn_name as buyer,j.title as unit')
            ->join('category b','a.branch_id=b.id','left')
            ->join('category c','a.business_type_id=c.id','left')
            ->join('client d','a.client_id=d.id','left')
            ->join('supplier e','a.supplier_id=e.id','left')
            ->join('supplier f','a.supplier_id2=f.id','left')
            ->join('user g','a.operator_id=g.id','left')
            ->join('product_type h','a.category_id=h.id','left')            
            ->join('user i','a.buyer_id=i.id','left')     
            ->join('category j','a.unit_id=j.id','left')      
            ->where($where)->page($page,$pageCount)->order('id','desc')->select();
        foreach ($data as &$v) {                  
            $v['payments'] = Db::name('payment')->where(['type'=>$v['type'],'outlay_id'=>$v['id']])->select(); 
            $v['instocknum'] = Db::name('stock')->where(['type'=>$v['type'],'buy_id'=>$v['id']])->sum('num');
        }
        $count = BuyModel::alias('a')->where($where)->count(); 
        return ["list"=>$data,"count"=>$count];
    } 
    
    public function listInfo($id){
        $buys = BuyModel::alias('a')
            ->field('a.*,b.title as branch,c.title as business_type,d.company_name as client,e.supplier_name as supplier,f.supplier_name as supplier2,g.cn_name as operator,h.name as category,i.cn_name as buyer,j.title as unit')
            ->join('category b','a.branch_id=b.id','left')
            ->join('category c','a.business_type_id=c.id','left')
            ->join('client d','a.client_id=d.id','left')
            ->join('supplier e','a.supplier_id=e.id','left')
            ->join('supplier f','a.supplier_id2=f.id','left')
            ->join('user g','a.operator_id=g.id','left')
            ->join('product_type h','a.category_id=h.id','left')            
            ->join('user i','a.buyer_id=i.id','left')    
            ->join('category j','a.unit_id=j.id','left')         
            ->where('a.id',$id)->select();        
        foreach ($buys as &$v) {
            $v['payments'] = Db::name('payment')->where(['type'=>$v['type'],'outlay_id'=>$v['id']])->select();  
        }
              
        return $buys;
    }

    public function infoBySn($sn){
        $buys = BuyModel::alias('a')
            ->field('a.*,b.title as branch,c.title as business_type,d.company_name as client,e.supplier_name as supplier,f.supplier_name as supplier2,g.cn_name as operator,h.name as category,i.cn_name as buyer,j.title as unit')
            ->join('category b','a.branch_id=b.id','left')
            ->join('category c','a.business_type_id=c.id','left')
            ->join('client d','a.client_id=d.id','left')
            ->join('supplier e','a.supplier_id=e.id','left')
            ->join('supplier f','a.supplier_id2=f.id','left')
            ->join('user g','a.operator_id=g.id','left')
            ->join('product_type h','a.category_id=h.id','left')            
            ->join('user i','a.buyer_id=i.id','left')    
            ->join('category j','a.unit_id=j.id','left')          
            ->where('sn',$sn)->select(); 
        foreach ($buys as &$v) {    
            $v['payments'] = Db::name('payment')->where(['type'=>$v['type'],'outlay_id'=>$v['id']])->select(); 
            $v['stockNum'] = Db::name('stock')->where(['type'=>$v['type'],'buy_id'=>$v['id']])->sum('num');
        }
        return $buys;
    }

    public function info($id){
        $buys = BuyModel::alias('a')
            ->field('a.*,b.title as branch,c.title as business_type,d.company_name as client,e.supplier_name as supplier,f.supplier_name as supplier2,g.cn_name as operator,h.name as category,i.cn_name as buyer,j.title as unit')
            ->join('category b','a.branch_id=b.id','left')
            ->join('category c','a.business_type_id=c.id','left')
            ->join('client d','a.client_id=d.id','left')
            ->join('supplier e','a.supplier_id=e.id','left')
            ->join('supplier f','a.supplier_id2=f.id','left')
            ->join('user g','a.operator_id=g.id','left')
            ->join('product_type h','a.category_id=h.id','left')            
            ->join('user i','a.buyer_id=i.id','left')
            ->join('category j','a.unit_id=j.id','left')               
            ->where('a.id',$id)->find();        
        $buys['payments'] = Db::name('payment')->where(['type'=>$buys['type'],'outlay_id'=>$buys['id']])->select();         
        return $buys;
    }

    public function save($params){   
        if(!empty($params['category_id'])){
            $productType = Db::name('product_type')->where('id',$params['category_id'])->find();     
            $params['product_no'] =  $productType['code'].'-'.$params['payment_no'];
        }
        
        if(empty($params['id'])){            
            $data = BuyModel::create($params); 
        }else{
            $data = BuyModel::update($params, ['id' => $params['id']]); 
        }        
        return $data;
    }

    public function delete($params){   
        Db::startTrans();
        try {
            $single = Db::name('buy')->where('id',$params['id'])->find();           
            $payment_data = Db::name('payment')->where(["outlay_id"=>$single["id"],"type"=>$single['type']])->select();
            foreach($payment_data as $p){
                Db::name('payment')->where(["id"=>$p["id"],"type"=>$p['type']])->delete();
            } 
            BuyModel::destroy($params['id']);
            // 提交事务
            Db::commit();           
        } catch (\Exception $e) {
            // 回滚事务
            Db::rollback();
        }           
    }

    public function csv($params){
        $where=[];        
        if(!empty($params['typeid'])){
            $where[] = ['type','=',$params['typeid']];   
        }
        if(!empty($params['keyword'])){
            $where[] = ['content','like',"%{$params['keyword']}%"];   
        }
        $data = BuyModel::alias('a')
            ->field('a.*,b.title as branch,c.title as business_type,d.company_name as client,e.supplier_name as supplier,f.supplier_name as supplier2,g.cn_name as operator,h.name as category,i.cn_name as buyer,j.title as unit')
            ->join('category b','a.branch_id=b.id','left')
            ->join('category c','a.business_type_id=c.id','left')
            ->join('client d','a.client_id=d.id','left')
            ->join('supplier e','a.supplier_id=e.id','left')
            ->join('supplier f','a.supplier_id2=f.id','left')
            ->join('user g','a.operator_id=g.id','left')
            ->join('product_type h','a.category_id=h.id','left')            
            ->join('user i','a.buyer_id=i.id','left')     
            ->join('category j','a.unit_id=j.id','left')          
            ->where($where)->order('id','desc')->select();
        foreach ($data as &$v) {                     
            $v['payments'] = Db::name('payment')->where(['type'=>$v['type'],'outlay_id'=>$v['id']])->select(); 
        }
        return $data;
    } 
}